using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using JoinViewProj;

// added by C.Chee
using MySql.Data.MySqlClient; 

namespace WebMenu
{
    public partial class CM_byShops : System.Web.UI.Page
    {
        String myConnectionString;
        MySqlDataReader dataReader;
        MySqlConnection myConn;
        private const string ASCENDING = " ASC";
        private const string DESCENDING = " DESC";
        JoinView jvMenu;


        protected void myInit() {
            //myConnectionString = "Database=dbcheckmenu;Data Source=localhost;User Id=checkmenu;Password=mysql";
            //myConnectionString = "Database=test;Data Source=localhost;User Id=root;Password=mysql";
            string sDBname = "";
            string sDBuser = "";
            string sDBpass = "";
            string sDBIPaddress = "";
            Config config = new Config();
            bool btest = false;
            config.readConfig(ref sDBname, ref sDBuser, ref sDBpass, ref sDBIPaddress, ref btest);
            myConnectionString = "Database=" + sDBname + ";Data Source=" + sDBIPaddress + ";User Id=" + sDBuser + ";Password=" + sDBpass + "";

            myConn = new MySqlConnection(myConnectionString);
            myConn.Open();
            tb_Debug.Visible = false;   // true;
            //tb_Debug.Text = myConnectionString;
        }


        protected void myFinal() {
            // Close the connection when done with it.
            myConn.Close();
        }

        protected void fillSuburbDropList()
        {
            string myInsertQuery = "SELECT  distinct (suburb) FROM shop"; //  WHERE shopID > 2";
            MySqlCommand myCommand = new MySqlCommand(myInsertQuery, myConn);

            MySqlDataAdapter myAdapt = new MySqlDataAdapter(myCommand);
            DataSet ds = new DataSet();
            myAdapt.Fill(ds, "shop");
            
            // Add extra rows
            DataRow rowNew = null;
            rowNew = ds.Tables["shop"].NewRow();
            rowNew["suburb"] = "All";
            ds.Tables["shop"].Rows.InsertAt(rowNew, 0);
             
            //Bind
            suburbDropList.DataSource = ds.Tables["shop"];
            suburbDropList.DataTextField = "suburb";
            suburbDropList.DataValueField = "suburb";
            suburbDropList.DataBind();
        }

        protected void fillCuisineDropList(string cuisineSelected)
        {
            string myInsertQuery = "SELECT  distinct (cuisine) FROM shop"; 
            MySqlCommand myCommand = new MySqlCommand(myInsertQuery, myConn);

            MySqlDataAdapter myAdapt = new MySqlDataAdapter(myCommand);
            DataSet ds = new DataSet();
            myAdapt.Fill(ds, "shop");

            // Add extra rows
            DataRow rowNew = null;
            rowNew = ds.Tables["shop"].NewRow();
            rowNew["cuisine"] = "All";
            ds.Tables["shop"].Rows.InsertAt(rowNew, 0);

            cuisineDropList.DataSource = ds.Tables["shop"];
            cuisineDropList.DataTextField = "cuisine";
            cuisineDropList.DataValueField = "cuisine";
            cuisineDropList.DataBind();
            cuisineSelected = cuisineDropList.SelectedValue;
        }

        protected void fillShopDropList(string sSuburb, string sCuisine) //, string shopSelected)
        {
            this.updateShop("");
//            string myInsertQuery = "SELECT  name, shopID FROM shop where cuisine = '"+sCuisine+"' AND suburb ='"+sSuburb+"'";
//            string myInsertQuery = "SELECT  name, shopID FROM shop where cuisine = '" + sCuisine + "' AND suburb ='" + sSuburb + "' AND active ='1'" ;
            string myInsertQuery = null;
            if (sSuburb.Equals("All") && sCuisine.Equals("All"))
            {
                myInsertQuery = "SELECT  name, shopID FROM shop where active ='1'" ;
            } else if (sSuburb.Equals("All") )
            {
                myInsertQuery = "SELECT  name, shopID FROM shop where cuisine = '" + sCuisine + "' AND active ='1'" ;
            }else if (sCuisine.Equals("All") ){
                myInsertQuery = "SELECT  name, shopID FROM shop where suburb = '" + sSuburb + "' AND active ='1'" ;
            } else{
                myInsertQuery = "SELECT  name, shopID FROM shop where cuisine = '" + sCuisine + "' AND suburb ='" + sSuburb + "' AND active ='1'" ;
            }
            
            MySqlCommand myCommand = new MySqlCommand(myInsertQuery, myConn);

            MySqlDataAdapter myAdapt = new MySqlDataAdapter(myCommand);
            DataSet ds = new DataSet();
            myAdapt.Fill(ds, "shop");

            int iCount = ds.Tables["shop"].Rows.Count;

            shopDropList.DataSource = ds.Tables["shop"];
            shopDropList.DataTextField = "name";
            shopDropList.DataValueField = "shopID";
            shopDropList.DataBind();

            shopDropList.AppendDataBoundItems = true;
            if (iCount < 1)
            {
                ListItem li = new ListItem("-none-", "");
                shopDropList.Items.Add(li);
            }
            else {
                ListItem li = new ListItem("-select-", "");
                shopDropList.Items.Insert(0, li);  //.Add(li);            
            }
            shopDropList.AppendDataBoundItems = false;
                //shopSelected = cuisineDropList.SelectedValue;
        }

        protected void updateShop(string sShop)
        {
            string myInsertQuery = "SELECT  * FROM shop where name = '" + sShop + "'";
            MySqlCommand myCommand = new MySqlCommand(myInsertQuery, myConn);

            MySqlDataAdapter myAdapt = new MySqlDataAdapter(myCommand);
            DataSet ds = new DataSet();
            myAdapt.Fill(ds, "shop");

            shopDataList.DataSource = ds.Tables["shop"];
            this.DataBind();

            //if (WUC_ToggleBtn_ShopDetails.toggleState == WUC_ToggleButton.ToggleButtonState.On &&
            //    sShop != "")
            //{
            //    WUC_ToggleBtn_ShopDetails.toggleState = WUC_ToggleButton.ToggleButtonState.Off;
            //    WUC_ToggleBtn_ShopDetails_eventToggle_Off(this, new EventArgs());
            //}
            //else
            //{
            //    WUC_ToggleBtn_ShopDetails.toggleState = WUC_ToggleButton.ToggleButtonState.Off;
            //}
    //        if (WUC_ToggleBtn_ShopDetails.toggleState == WUC_ToggleButton.ToggleButtonState.Off &&
    //sShop != "")
    //        {
    //            WUC_ToggleBtn_ShopDetails.toggleState = WUC_ToggleButton.ToggleButtonState.On;
    //            WUC_ToggleBtn_ShopDetails_eventToggle_On(this, new EventArgs());
    //        }
        }

        #region Web Form Designer generated code
        override protected void OnInit(EventArgs e)
        {
            //
            // CODEGEN: This call is required by the ASP.NET Web Form Designer.
            //
            InitializeComponent();
            base.OnInit(e);
            WUC_ToggleBtn_ShopDetails.btnToggle01_Off();
        }

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {   
            cuisineDropList.SelectedIndexChanged += new EventHandler(cuisineDropList_SelectedIndexChanged);
            suburbDropList.SelectedIndexChanged += new EventHandler(suburbDropList_SelectedIndexChanged);
            shopDropList.SelectedIndexChanged += new EventHandler(shopDropList_SelectedIndexChanged);
            ////this.Load += new System.EventHandler(this.Page_Load);

            // Initialize handlers for Toggle button for Shop Details
            WUC_ToggleBtn_ShopDetails.eventToggle_On +=new EventHandler(WUC_ToggleBtn_ShopDetails_eventToggle_On);
            WUC_ToggleBtn_ShopDetails.eventToggle_Off +=new EventHandler(WUC_ToggleBtn_ShopDetails_eventToggle_Off);
             
        }

        /// <summary>
        /// Listener for the Shop drop down list
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        void shopDropList_SelectedIndexChanged(object sender, EventArgs e)
        {
            WUC_ToggleBtn_ShopDetails.btnToggle01_Off();
            //this.updateShop(shopDropList.SelectedValue);
            this.publishShopPosition(shopDropList.SelectedValue);  // this value is the shopID column
            this.updateMenu(gvMenu);
        }

        /// <summary>
        /// Get the Latitude and Longitude of the given shop and publish it to the web page.
        /// sShop is the shopID column
        /// </summary>
        public void publishShopPosition(string sShop)
        {
            string mySelectQuery = "SELECT  * FROM shop where shopID = '" + sShop + "'";
            MySqlCommand myCommand = new MySqlCommand(mySelectQuery , myConn);
            MySqlDataAdapter myAdapt = new MySqlDataAdapter(myCommand);
            DataSet ds = new DataSet();
            myAdapt.Fill(ds, "shopPosn"); 
            DataRow drr = ds.Tables["shopPosn"].Rows[0]; //.Columns["latitude"].ToString();
            string sShopName;
            sShopName = (string)drr["name"];       //+ "Hello\r\n";
            string sShopAddress = (string) drr["address"] + "\r\n" + (string) drr["suburb"] + (string) drr["state"] ;
            hidShopDetails.Value = sShopName;  // +sShopAddress;
            hidLatitude.Value = (string)drr["latitude"];
            hidLongitude.Value = (string)drr["longitude"];
        }


        /// <summary>
        /// Listener for the Suburb drop down list
        /// Page_Load is automatically called first before this, hence need to explicitly call 
        /// Page_Load again to update/clear page.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        void suburbDropList_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.fillShopDropList(suburbDropList.SelectedValue, cuisineDropList.SelectedValue);
            //this.updateShop("");   
            this.Page_Load(sender, e);
        }

        /// <summary>
        /// Listener for the Cuisine drop down list
        /// Page_Load is automatically called first before this, hence need to explicitly call 
        /// Page_Load again to update/clear page.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        void cuisineDropList_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.fillShopDropList(suburbDropList.SelectedValue, cuisineDropList.SelectedValue);
            this.Page_Load(sender, e);
        }
        #endregion


        
        #region Testing GridView
        //protected void updateMenu() {
        //    string myInsertQuery = "SELECT  * FROM menu";
        //    MySqlCommand myCommand = new MySqlCommand(myInsertQuery, myConn);

        //    MySqlDataAdapter myAdapt = new MySqlDataAdapter(myCommand);
        //    DataSet ds = new DataSet();
        //    myAdapt.Fill(ds, "menu");

        //    gvMenu.AutoGenerateColumns = true;
        //    gvMenu.DataSource = ds.Tables["menu"];
        //    this.DataBind();
        //}
        #endregion

        protected void updateMenu(GridView gv)
        {
            string sShopID;
            DataSet dsTest = new DataSet();

            sShopID = shopDropList.SelectedValue;
            if (!sShopID.Equals(""))
            {
                // Copy table to DataSet dsTest
                //string mySelect = "SELECT  * FROM shop";
                //MySqlCommand myCommand = new MySqlCommand(mySelect, myConn);
                //MySqlDataAdapter myAdapt = new MySqlDataAdapter(myCommand);
                //myAdapt.Fill(dsTest, "shop");

                //// Copy table to DataSet dsTest
                //myCommand.CommandText = "SELECT  * FROM food";
                //myAdapt.Fill(dsTest, "food");

                //// Copy table to DataSet dsTest
                //myCommand.CommandText = "SELECT  * FROM menu";
                //myAdapt.Fill(dsTest, "menu");


                //// Relations
                //DataRelation Shop_Menu = new DataRelation("Shop_Menu", dsTest.Tables["shop"].Columns["shopID"],
                //    dsTest.Tables["menu"].Columns["shopID"]);
                //DataRelation Food_Menu = new DataRelation("Food_Menu", dsTest.Tables["food"].Columns["foodID"],
                //    dsTest.Tables["menu"].Columns["foodID"]);
                //dsTest.Relations.Add(Shop_Menu);
                //dsTest.Relations.Add(Food_Menu);


                //jvMenu = new JoinView(dsTest.Tables["menu"],
                //    "Food_Menu.category Category, Food_Menu.name Name, price, Food_Menu.description",
                //    "shopID='" + sShopID + "'", "price DESC");

                // Copy table to DataSet dsTest
                //myCommand.CommandText = "SELECT  foodname as Foods, category as Category, description, price FROM foodmenu where shopID = '" + sShopID + "'";
                //myAdapt.Fill(dsTest, "food_copy");
                string mySelect = "SELECT  foodname as Foods, category as Category, description, price FROM foodmenu where shopID = '" + sShopID + "'";
                MySqlCommand myCommand = new MySqlCommand(mySelect, myConn);
                MySqlDataAdapter myAdapt = new MySqlDataAdapter(myCommand);
                myAdapt.Fill(dsTest, "food_copy");

                // http://msdn.microsoft.com/en-us/library/ms178296.aspx
                //gridview column width
                
                gv.AutoGenerateColumns = true;
                gv.DataSource = dsTest.Tables["food_copy"]; //jvMenu;
                gv.DataBind();

                //gv.da
                DataControlFieldCollection dcfc = new DataControlFieldCollection();
                int iCC = gv.Columns.Count;
                //gv.Columns[0].ItemStyle.Width = Unit.Parse("10");
                //for (int i = 0; i < gv.Columns.Count; i++)
                //{
                //    gv.Columns[i].ItemStyle.Width = 10;
                //}

                // STORED procedure in mySQL with .Net
// cuisine type
//select distinct cuisine 
//from shop

// select category
//Select distinct category
//from foodmenu
//inner join shop
//on shop.shopID = foodmenu.shopID
//where shop.cuisine='Chinese'

// Select Food
//Select foodname, price, shop.name
//from foodmenu
//inner join shop
//on shop.shopID = foodmenu.shopID
//where shop.cuisine='Indian'
//and category = 'Chicken'



//                select foodname, shopID, price
//from foodmenu
//where category='beef' and shopID in
//(
//select shopID 
//from shop
//where cuisine in ('Cantonese', 'Chinese')
//)



//inner join foodmenu
//on shop.shopID = foodmenu.shopID


//where shop.cuisine in ('Cantonese', 'Chinese')


            }
            else {
                gv.DataSource = null;
                gv.DataBind();
            }
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            string shopSelect;
            string cuisineSelect = "";
            string suburbSelect;
            this.myInit();
            if (this.IsPostBack == false)  // First Time run
            {
                this.fillSuburbDropList();
                this.fillCuisineDropList(cuisineSelect);
                this.fillShopDropList(suburbDropList.SelectedValue, cuisineDropList.SelectedValue);
                //this.updateShop(shopDropList.SelectedValue);
            }
            else {
                //butShopUpdate.Visible = false;
            }
            //this.updateShop(shopDropList.SelectedValue);
            this.updateMenu(gvMenu);
            this.myFinal();
              
        }

        private void WUC_ToggleBtn_ShopDetails_eventToggle_On(object sender, EventArgs ev) {
            shopDataList.Visible = true;
            butShopUpdate_Click(sender, ev);
            this.map.Visible = true;
            this.butMap.Disabled = false;
            //this.map.Style = "width: 400px; height: 400px";
        }
        private void WUC_ToggleBtn_ShopDetails_eventToggle_Off(object sender, EventArgs ev)
        {
            shopDataList.Visible = false;
            this.map.Visible = false;
            this.butMap.Disabled = true;
            //this.map.Style = "width: 00px; height: 00px";
            
        }

        protected void butShopUpdate_Click(object sender, EventArgs e)
        {
            string sShopName;
            ListItem liShop = shopDropList.SelectedItem;
            if (liShop == null || shopDropList.SelectedValue == "")
            {
                this.updateShop("");  // .SelectedValue);
                tb_Debug.Text = "No Shop details are found";
            }
            else {
                sShopName = liShop.Text;
                this.updateShop(sShopName);  // .SelectedValue);
                tb_Debug.Text = "Hello World " + shopDropList.SelectedValue;
            }
        }

        #region gridview manipulation
        public SortDirection gvSortDirection {
            get
            {
                if (ViewState["sortDirection"] == null)
                    ViewState["sortDirection"] = SortDirection.Ascending;
                return (SortDirection)ViewState["sortDirection"];
            }
            set { ViewState["sortDirection"] = value; }
        }
        protected void gvMenu_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            gvMenu.PageIndex = e.NewPageIndex;
            gvMenu.DataBind();
        }
        protected void gvMenu_Sorting(object sender, GridViewSortEventArgs e)
        {
            string sortExpression = e.SortExpression;
            string strSort = null;
            if (gvSortDirection == SortDirection.Ascending)
            {
                gvSortDirection = SortDirection.Descending;
                //SortJVMenu(sortExpression, DESCENDING, gvMenu);
                strSort = sortExpression + " " + DESCENDING;
            }
            else
            {
                gvSortDirection = SortDirection.Ascending;
                //SortJVMenu(sortExpression, ASCENDING, gvMenu);
                strSort = sortExpression + " " + ASCENDING;
            }
            DataTable dc1 = (DataTable)gvMenu.DataSource;
            dc1.DefaultView.Sort = strSort;
            gvMenu.DataBind();
        }
        private void SortJVMenu(string sortExpression, string direction, GridView gv)
        {
            //if (jvMenu != null)
            //{
            //    jvMenu.Sort = sortExpression + direction;
            //    gv.DataSource = jvMenu;
            //}
            string sDirection = "";
            if (direction == ASCENDING) { sDirection = "ASC"; }
            else if (direction == DESCENDING) { sDirection = "DESC"; }
            string strSort = sortExpression + " " + sDirection;

            DataTable dc1 = (DataTable)gv.DataSource;
            dc1.DefaultView.Sort = strSort;
            gv.DataBind();
        }
        #endregion

        protected void Grid_RowDataBound(Object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType != DataControlRowType.DataRow) return;
            GridView gv = (GridView)sender;
            e.Row.Cells[0].Width = 100;
            e.Row.Cells[1].Width = 400;
            e.Row.Cells[2].Width = 50;
            e.Row.Cells[3].Width = 800;
            //e.Row.Cells[5].Width = 800;

        }

        /// <summary>
        /// Obsolete Feature
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void butMenuUpdate_Click(object sender, EventArgs e)
        {
            this.updateMenu(gvMenu);
        }


    }  // class CM_byShops 
}   // namespace WebMenu

#region old code for reference
//DataTable dtMenu = new DataTable("resultMenu");
//DataColumnCollection col1 = dsTest.Tables["food"].Columns;
//DataColumn dcc;
//dtMenu = dsTest.Tables["food"].Clone();
//foreach (DataColumn iCol in col1) {
//    dcc = new DataColumn();
//    dcc = iCol;
//    dtMenu.Columns.Add(dcc);
//}
//dtMenu.Columns.Add(dsTest.Tables["menu"].Columns["price"]);

//DataRow dr1 = dsTest.Tables["shop"].Rows[4-1];
//tb_Debug.Text = dr1["name"].ToString();


//foreach (DataRow rowShopMenu in dr1.GetChildRows("Shop_Menu")) {
//    tb_Debug.Text += rowShopMenu["price"].ToString();
//    foreach (DataRow rowFood in rowShopMenu.GetParentRows("Food_Menu"))
//    {
//        tb_Debug.Text += rowFood["name"].ToString();
//        dtMenu.Rows.Add(rowFood);

//        TestMenu.DataSource = new DataView(dtMenu);
//        TestMenu.DataBind();

//    }
//}

//// Create another table
//DataTable dtt = dsTest.Tables["menu"];
//// Delete rows is price > 8.0
//foreach (DataRow drow in dtt.Rows)
//{
//    if (float.Parse(drow["price"].ToString()) > 8.0)
//    {
//        drow.Delete();
//    }
//}
//dtt.AcceptChanges();
//// Write filtered table to Grid menu
//gvMenu.DataSource = dtt;
//gvMenu.AutoGenerateColumns = true;
//this.DataBind();
#endregion